#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   curd.py
@Time    :   2021/02/08 00:12:14
@Author  :   superjin
@Version :   1.0
'''

from hashlib import new
from os import name
from typing import Any, Optional, List
from sqlalchemy import func,and_
from sqlalchemy.orm import Session
from models.user import User
from models.course import Course
from models.reservation import Reservation
from models.participant import Participant
from models.classroom import Classroom

class CRUDCourse:
    def add_course(self,db,name,teacher_id,times,class_id):
        course = Course(name,teacher_id,times,class_id)
        db.add(course)
        db.commit()
        db.refresh(course)
        return course
    def modify_course(self):
        pass
    def delete_course(self):
        pass
    def list_course_by_id(self, db: Session, page:int, pageSize:int,teacher_id):
        data = db.query(Course).filter_by(
            teacher_id=teacher_id).offset((page-1)*pageSize).limit(pageSize).all()
        total = db.query(func.count(Course.course_id)).scalar()
        return data,total
    def list_course(self, db: Session, page:int, pageSize:int):
        data = db.query(Course).offset((page-1)*pageSize).limit(pageSize).all()
        total = db.query(func.count(Course.course_id)).scalar()
        return data,total
    def get_course(self,db,course_id):
        res = db.query(Course).filter_by(
            course_id=course_id).first()
        return res
    def list_course_record(self,db,page:int, page_size:int, creator_id = 0):
        if creator_id !=0:
            res = db.query(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Reservation.course_id,
                func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                User.name.label('creater_name'),
                Course.course_id,
                Course.name.label('course_name'),
                func.count(Participant.participant_id).label('participant_count')
            ).filter_by(creator_id=creator_id).filter_by(reservation_type='class')\
             .join(User, User.user_id == Reservation.creator_id) \
             .join(Classroom, Reservation.classroom_id == Classroom.classroom_id) \
             .join(Course, Reservation.course_id == Course.course_id) \
             .join(Participant, Participant.reservation_id == Reservation.reservation_id, isouter=True) \
             .group_by(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Classroom.room_number,
                Classroom.building_name,
                User.name
             ) \
             .offset((page - 1) * page_size) \
             .limit(page_size) \
             .all()

        # 查询总记录数
            total = db.query(func.count(Reservation.reservation_id)).filter_by(
                creator_id=creator_id).filter_by(
                    reservation_type='class').scalar()

            return res, total
            
        res = db.query(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Reservation.course_id,
                func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                User.name.label('creater_name'),
                Course.course_id,
                Course.name.label('course_name'),
                func.count(Participant.participant_id).label('participant_count')
            ).join(User, User.user_id == Reservation.creator_id) \
             .join(Classroom, Reservation.classroom_id == Classroom.classroom_id) \
             .join(Course, Reservation.course_id == Course.course_id) \
             .join(Participant, Participant.reservation_id == Reservation.reservation_id, isouter=True) \
             .group_by(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Classroom.room_number,
                Classroom.building_name,
                User.name
             ) \
             .offset((page - 1) * page_size) \
             .limit(page_size) \
             .all()

        # 查询总记录数
        total = db.query(func.count(Reservation.reservation_id)).scalar()

        return res, total
    def list_course_record_by_name(self,db,page:int, page_size:int, creator_id = 0,course_name:str = ''):
        if creator_id !=0:
            res = db.query(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Reservation.course_id,
                func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                User.name.label('creater_name'),
                Course.course_id,
                Course.name.label('course_name'),
                func.count(Participant.participant_id).label('participant_count')
            ).filter_by(creator_id=creator_id).filter_by(reservation_type='class')\
             .join(User, User.user_id == Reservation.creator_id) \
             .join(Classroom, Reservation.classroom_id == Classroom.classroom_id) \
             .join(Course, Reservation.course_id == Course.course_id).filter(Course.name == course_name) \
             .join(Participant, Participant.reservation_id == Reservation.reservation_id, isouter=True) \
             .group_by(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Classroom.room_number,
                Classroom.building_name,
                User.name
             ) \
             .offset((page - 1) * page_size) \
             .limit(page_size) \
             .all()

        # 查询总记录数
            total = db.query(func.count(Reservation.reservation_id)).filter_by(
                creator_id=creator_id).filter_by(
                    reservation_type='class').join(
                        Course, Reservation.course_id == Course.course_id).filter(
                            Course.name == course_name).scalar()

            return res, total
            
        res = db.query(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Reservation.course_id,
                func.concat(Classroom.building_name,'-',Classroom.room_number).label('location'),
                User.name.label('creater_name'),
                Course.course_id,
                Course.name.label('course_name'),
                func.count(Participant.participant_id).label('participant_count')
            ).join(User, User.user_id == Reservation.creator_id) \
             .join(Classroom, Reservation.classroom_id == Classroom.classroom_id) \
             .join(Course, Reservation.course_id == Course.course_id).filter(Course.name == course_name) \
             .join(Participant, Participant.reservation_id == Reservation.reservation_id, isouter=True) \
             .group_by(
                Reservation.reservation_id,
                Reservation.reservation_type,
                Reservation.start_time,
                Reservation.end_time,
                Reservation.creator_id,
                Reservation.status,
                Reservation.description,
                Classroom.room_number,
                Classroom.building_name,
                User.name
             ) \
             .offset((page - 1) * page_size) \
             .limit(page_size) \
             .all()

        # 查询总记录数
        total = db.query(func.count(Reservation.reservation_id)).join(
            Course, Reservation.course_id == Course.course_id).filter(Course.name == course_name).scalar()

        return res, total
course = CRUDCourse()
